import os
import pandas as pd
from quant_researcher.quant.datasource_fetch.crypto_api.coinglass import get_recent_funding_rate
from utils import df_into_db


DATA_DIR = f'E:\\指标数据'  # 用于存放测试数据

file_path = os.path.join(DATA_DIR, f'funding_rate')

for symbol in ['BTC-USDT', 'BTC-USD']:
    file_name = os.path.join(file_path, f'{symbol}_composite_funding_rate')
    if os.path.exists(f'{file_name}.xlsx'):
        all_history_data = pd.read_excel(f'{file_name}.xlsx')
        all_history_data.sort_values(by='timestamp', inplace=True)
        all_history_data.set_index('datetime', drop=True, inplace=True)
        latest_date = all_history_data.index[-1]
    else:
        all_history_data = pd.DataFrame()
        latest_date = '2010-01-01 00:00:00'

    file_name = os.path.join(file_path, f'coinglass_{symbol}_funding_rate')
    coinglass_history_data = pd.read_excel(f'{file_name}.xlsx')
    coinglass_history_data.sort_values(by='timestamp', inplace=True)
    coinglass_history_data.set_index('datetime', drop=True, inplace=True)

    timezone = 'Asia/Shanghai'
    if symbol == 'BTC-USDT':
        asset = 'BTC'
        market_type = 'U'
    elif symbol == 'BTC-USD':
        asset = 'BTC'
        market_type = 'C'
    else:
        raise ValueError
    recent_coinglass_df = get_recent_funding_rate(asset=asset, market_type=market_type)
    if recent_coinglass_df is not None:
        recent_coinglass_df['timestamp'] = recent_coinglass_df.apply(lambda x: int(x['timestamp'] / 1000), axis=1)
        recent_coinglass_df['date'] = pd.to_datetime(recent_coinglass_df['timestamp'], unit='s').dt.tz_localize(
            'UTC').dt.tz_convert(timezone)
        recent_coinglass_df['date'] = recent_coinglass_df['date'].dt.strftime('%Y-%m-%d')
        recent_coinglass_df['datetime'] = pd.to_datetime(recent_coinglass_df['timestamp'], unit='s').dt.tz_localize(
            'UTC').dt.tz_convert(timezone)
        recent_coinglass_df['datetime'] = recent_coinglass_df['datetime'].dt.strftime('%Y-%m-%d %H:%M:%S')
        recent_coinglass_df['time'] = pd.to_datetime(recent_coinglass_df['timestamp'], unit='s').dt.tz_localize(
            'UTC').dt.tz_convert(timezone)
        recent_coinglass_df['time'] = recent_coinglass_df['time'].dt.strftime('%H:%M:%S')
        recent_coinglass_df.set_index('datetime', inplace=True)
        recent_coinglass_df = recent_coinglass_df.iloc[:-1, ]  # 最后一条数据不完整，需要剔除

        all_coinglass_df = pd.concat(
            [coinglass_history_data.iloc[:-1, ], recent_coinglass_df.loc[coinglass_history_data.index[-1]:, ]])
    else:
        all_coinglass_df = coinglass_history_data
    all_coinglass_df.sort_index(inplace=True)
    all_coinglass_df.index.name = 'datetime'
    all_coinglass_df = all_coinglass_df.reset_index(drop=False)
    all_coinglass_df.drop(columns=['date', 'time'], inplace=True)
    all_coinglass_df.fillna(0, inplace=True)
    all_coinglass_df['symbol'] = symbol
    df_into_db(all_coinglass_df, db_name='funding_rate', table_name='coinglass_funding_rate')
    # file_path = os.path.join(DATA_DIR, f'funding_rate')
    # file_name = os.path.join(file_path, f'coinglass_{symbol}_funding_rate')
    # all_coinglass_df.to_excel(f'{file_name}.xlsx')